Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient
Imports AppsUtility.EncDec
Imports System.Web.Mail
Imports System.Text
Imports Microsoft.Office.Core
Imports System.Configuration

Public Class BMdatalogic
    Dim util As New AppsUtility.EncDec
    Public strCon As String = util.toDecrypt(System.Configuration.ConfigurationSettings.AppSettings("SQLString"))
    Public SaleAc As String = ConfigurationManager.AppSettings("GlSalesControlAccount")
    Public CostAc As String = ConfigurationManager.AppSettings("GlCostControlAccount")
    Public InventAc As String = ConfigurationManager.AppSettings("GlInventoryControlAccount")
    Public PurchaseAc As String = ConfigurationManager.AppSettings("GLPurchaseControlAccount")
    Public ArAc As String = ConfigurationManager.AppSettings("GlReceivableControlAccount")

    Public Function DefaultYear() As Integer
        If Now.Month <= 4 Then
            DefaultYear = Now.Year
        Else
            DefaultYear = Now.Year + 1
        End If
    End Function
    Public Function GetSqlConnection() As String
        Return strCon
    End Function
    Public Function ListFinancialYears() As DataSet
        Dim DS As DataSet = New DataSet("FinYears")
        Dim DT As DataTable = DS.Tables.Add("Years")
        DT.Columns.Add("YearNo", Type.GetType("System.String"))
        DT.Columns.Add("YearCode", Type.GetType("System.String"))
        Dim BaseYear As Integer = 2008
        Dim n As Integer
        For n = BaseYear To Now.Year + 1
            Dim drow As DataRow
            drow = DS.Tables(0).NewRow()
            drow("YearNo") = n.ToString.Trim
            drow("YearCode") = (n - 1).ToString.Trim + "-" + n.ToString.Trim
            DS.Tables(0).Rows.Add(drow)
        Next
        ListFinancialYears = DS
    End Function
    Public Function GetCatgeory() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from ProductGroup where GROUPLEVEL=3 ORDER BY GROUPNAME"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet
        Try
            da.Fill(ds)
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCatgeory = ds
    End Function
    Public Sub InsertDataStrategyIndicator(ByVal startindic As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "SELECT * FROM strategy_indicator_list where strategy_indicator='" & startindic & "'"
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet
        da.Fill(ds)
        If ds.Tables(0).Rows.Count = 0 Then
            strsql = "INSERT INTO strategy_indicator_list(strategy_indicator) VALUES('" & startindic & "')"
            Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
            sqlCon.Open()
            Try
                sqlCmd.CommandText = strsql
                sqlCmd.ExecuteNonQuery()
                MsgBox("strategy indicator : " & startindic & " added successfully!", MsgBoxStyle.Information, "Inventory Manager")
            Catch ex As SqlException
                MsgBox(ex.ToString())
            End Try
        Else
            MsgBox("strategy indicator : " & startindic & " already exists! Please try another name.", MsgBoxStyle.Information, "Inventory Manager")
        End If
    End Sub
    Public Function GetStrategicIndicator() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from strategy_indicator_list"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet
        Try
            da.Fill(ds)
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetStrategicIndicator = ds
    End Function
    Public Function GetClusterDetails() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from cluster_list"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet
        Try
            da.Fill(ds)
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        GetClusterDetails = ds
        sqlCon.Close()
    End Function
    Public Function GetTacticsDetails() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from Tactics_List"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet
        Try
            da.Fill(ds)
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        GetTacticsDetails = ds
        sqlCon.Close()
    End Function
    Public Sub InsertDataCluster(ByVal clustxt As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "SELECT * FROM cluster_list where cluster_name='" & clustxt & "'"
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet
        da.Fill(ds)
        If ds.Tables(0).Rows.Count = 0 Then
            strsql = "INSERT INTO cluster_list(cluster_name) VALUES('" & clustxt & "')"
            Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
            sqlCon.Open()
            Try
                sqlCmd.CommandText = strsql
                sqlCmd.ExecuteNonQuery()
                MsgBox("Cluster : " & clustxt & " added successfully!", MsgBoxStyle.Information, "Inventory Manager")
            Catch ex As SqlException
                MsgBox(ex.ToString())
            End Try
        Else
            MsgBox("Cluster : " & clustxt & " already exists! Please try another name.", MsgBoxStyle.Information, "Inventory Manager")
        End If
    End Sub
    Public Sub InsertDataTactics(ByVal clustxt As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "SELECT * FROM Tactics_List where tactics='" & clustxt & "'"
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet
        da.Fill(ds)
        If ds.Tables(0).Rows.Count = 0 Then
            strsql = "INSERT INTO Tactics_List(tactics) VALUES('" & clustxt & "')"
            Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
            sqlCon.Open()
            Try
                sqlCmd.CommandText = strsql
                sqlCmd.ExecuteNonQuery()
                MsgBox("Tactics : " & clustxt & " added successfully!", MsgBoxStyle.Information, "Inventory Manager")
            Catch ex As SqlException
                MsgBox(ex.ToString())
            End Try
        Else
            MsgBox("Tactics : " & clustxt & " already exists! Please try another name.", MsgBoxStyle.Information, "Inventory Manager")
        End If
    End Sub
    Public Sub InsertDataCategoryRole(ByVal catroltxt As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "SELECT * FROM category_role_list where category_role='" & catroltxt & "'"
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet
        da.Fill(ds)
        If ds.Tables(0).Rows.Count = 0 Then
            strsql = "INSERT INTO category_role_list(category_role) VALUES('" & catroltxt & "')"
            Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
            sqlCon.Open()
            Try
                sqlCmd.CommandText = strsql
                sqlCmd.ExecuteNonQuery()
                MsgBox("Category Role : " & catroltxt & " added successfully!", MsgBoxStyle.Information, "Inventory Manager")
            Catch ex As SqlException
                MsgBox(ex.ToString())
            End Try
        Else
            MsgBox("Category Role : " & catroltxt & " already exists! Please try another name.", MsgBoxStyle.Information, "Inventory Manager")
        End If
    End Sub
    Public Function GetCategoryRoles() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from category_role_list"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet
        Try
            da.Fill(ds)
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCategoryRoles = ds
    End Function
End Class
